
// PROJECT: PREPAYING UNDERWATER MORTGAGES DATA DOFILE


// A. ABSNET DATA

// I. SELECTING SAMPLE FROM DEC 2007 LOAN PERFORMANCE DATA

clear all

// Create project directory

cd "D:\Box\Box\Moussa\Research\Underwater Mortgages\stata_sas_codes\JFQA Codes and Data"

// Create a folder named "input_data" in work directory

use ".\input_data\lp_200712.dta" , clear // This data set was created from the ABSNet December 2007 loan performance data file by keeping the following variables: absnetloanfk loanstatusfk remainingterm beginningpoolbalance endingpoolbalance actualendingbalance delinquentdaysmba currentinterestrate prepaymentsinfull liquidations repurchases daysinbankruptcy daysinforeclosure daysinreo daystoliquidation shortsaleflag cleanflagmba conformingflag

rename absnetloanfk absnetloanpk 

merge 1:1 absnetloanpk using ".\input_data\loanchars.dta"  // This data set was created from the ABSNet loan setup data set by keeping: absnetloanpk loanid armflag absnetloandealfk balloonflag cbsa creditscore debttoincomeratio documenttypefk interestonlyflag loanoriginationdate loanpurposefk loantypefk negativeamortizationflag numberofunits originalappraisedvalue originalamortizationterm occupancystatusfk originalcltv originalinterestrate originalloanbalance originalltv originalsecuritizedbalance originalterm originator pmiflag prepaymentpenaltyflag propertycity propertystatefk propertytypefk propertyzip purchasepriceofproperty lienposition originalltv

keep if _merge==3
drop _merge

// Keeping Loans Originated between 2000 and 2007

gen origyear=year(loanoriginationdate)

tab origyear, m

keep if origyear>=2001 & origyear<=2007

// Keeping Continental States

drop if propertystatefk=="" | propertystatefk=="AP" | propertystatefk=="PR" | propertystatefk=="HI" | propertystatefk=="GU"| propertystatefk=="XX" | propertystatefk=="VI"

// Droping Second Liens

tab lienposition, m

keep if lienposition==1 | lienposition==2

// Dropping Missing Locations

drop if  cbsa==. | propertyzip==.

// Keeping 1 to 4-Units 4 and missings

tab numberofunits, m

keep if inlist(numberofunits,1,2,3,4,.)

// Keeping Loans between than $50,000 and 5 Million

drop if originalloanbalance==.

drop if lienposition==1 & (originalloanbalance<50000 | originalloanbalance>5000000)

drop if lienposition==2 & (originalloanbalance<10000 | originalloanbalance>5000000)

// Limiting LTV

drop if lienposition==1 & (originalltv<25 | originalltv>125)

drop if lienposition==2 & (originalltv<5 | originalltv>125)

// Credit Score

drop if creditscore==.

// Loan Purpose: Keeping cash-out refis, purchases, rate refis, and other

drop if lienposition==1 & loanpurposefk=="N" // Construction loans

// Appraisal Value: Keeping appraisals between 50,000 and 10,000,000

drop if lienposition==1 & (originalappraisedvalue<50000 | originalappraisedvalue>10000000)

drop if lienposition==2 & (originalappraisedvalue<5000 | originalappraisedvalue>10000000)

// Adding January 2008 Beginning Loan Balance

rename (loanstatusfk remainingterm beginningpoolbalance endingpoolbalance actualendingbalance delinquentdaysmba currentinterestrate prepaymentsinfull liquidations repurchases daysinbankruptcy daysinforeclosure daysinreo daystoliquidation shortsaleflag cleanflagmba conformingflag)(loanstatusfk_200712 remainingterm_200712 beginningpoolbalance_200712 endingpoolbalance_200712 actualendingbalance_200712 delinquentdaysmba_200712 currentinterestrate_200712 prepaymentsinfull_200712 liquidations_200712 repurchases_200712 daysinbankruptcy_200712 daysinforeclosure_200712 daysinreo_200712 daystoliquidation_200712 shortsaleflag_200712 cleanflagmba_200712 conformingflag_200712)

rename absnetloanpk  absnetloanfk 

merge 1:1 absnetloanfk using ".\input_data\lp_200801.dta", keepusing(beginningpoolbalance) // This data set was created from the ABSNet January 2008 loan performance data file by keeping the following variables: absnetloanfk beginningpoolbalance

drop if _merge==2
drop _merge

rename beginningpoolbalance beginningpoolbalance_200801

// Adding Vintage and Asset Type

rename absnetloandealfk absnetloandealpk

merge m:1 absnetloandealpk using  ".\input_data\deals.dta", keepusing(closingdate vintage assettype) // This data set was created from the ABSNet deals file by keeping the following variables: absnetloandealpk closingdate vintage assettype

drop if _merge==2
drop _merge

// First, create a folder named "output_files" in the work directory

save ".\output_data\loan_sample.dta", replace // Save this data set in the output folder

// II. SELECTING FROM ABSNET LOAN HISTORY DATA APPEARING IN SAMPLE

import sas using ".\input_data\loanhistory.sas7bdat", case(lower) clear // This data set was created with the SAS program titled "loanhistory_sas_dofile"; the orignal dataset is too large for STATA

format histstartdate histenddate bankruptcydate foreclosuredate reodate lewtanmodifieddate prepaymentdate liquidationdate repurchasedate %td

drop endyear startyear

gen year=year(histenddate)

label variable year "history end year"

gen month=month(histenddate)

label variable month "history end month"

gen date=mofd(histenddate)

label variable date "month of the day"

// Keeping Loans in Sample

merge 1:1 absnetloanfk using ".\output_data\loan_sample.dta", keepusing(absnetloanfk)

keep if _merge==3

drop _merge

save ".\output_data\sample_loanhistory.dta", replace


// III. GETTING LOAN BALANCE AND OTHER INFO ON HISTORY END MONTH

// 1. Combining Loan Sample and History

clear all

use ".\output_data\loan_sample.dta", clear

merge 1:1 absnetloanfk using ".\output_data\sample_loanhistory.dta"

keep if _merge==3

drop histloanstatus _merge

save ".\output_data\loansample_loanhistory.dta", replace

keep absnetloanfk histenddate

save ".\output_data\loan_sample_history_temp.dta", replace


// 2. Getting Last Six Performance Updates for Each Loan in Descending Order - Make sure you run the codes in 1 above before running this

// this section extract loan performance updates from Jan 2008 (yearmonth 576) to March 2016 (yearmonth 674) -- yearmonth are number of months starting in Jan 1960. For each loan in our sample, we collect the performance data for the last 6 months, the final month and the preceeding 5 months. The code below only loops over 6 months from January 2008 (i=576) to January 2008 (i=). 

clear all

// Run the following loop over the entire sample period by changing the starting value in the i loop to 674 as noted below. But make sure you create the necessary performance files properly named using year-month index for the looping.

forvalues i=576(-1)576{ 	// change the loop only to  i=674(-1)576

	forvalues j=0/5{
		
	local k=`i'-`j'
		
	// Keeping matched loans

	use ".\output_data\loan_sample_history_temp.dta", clear
    
	merge 1:1 absnetloanfk using  ".\input_data\loan`k'.dta", keepusing(absnetloanfk reportingperiodfk loanstatusfk remainingterm beginningpoolbalance endingpoolbalance actualendingbalance delinquentdaysmba currentinterestrate prepaymentsinfull liquidations repurchases daysinbankruptcy daysinforeclosure daysinreo daystoliquidation shortsaleflag) // The datasets used in this loop are the original ABSNet monthly loan performance datasets properly renamed using STATA year-month (starting in January 1960) for indexing

	keep if _merge==3

	drop _merge

	gen t=`j'

	save ".\output_data\match`j'.dta", replace 
	}

// Merging matched loans

use ".\output_data\match0.dta", clear

append using ".\output_data\match1.dta" ".\output_data\match2.dta" ".\output_data\match3.dta" ".\output_data\match4.dta" ".\output_data\match5.dta"

sort absnetloanfk t

by absnetloanfk: gen t_series=_n

by absnetloanfk: egen t_max=max(t_series)

drop t

save ".\output_data\loan`i'.dta", replace

// Keeping unmatched loans from sample

use ".\output_data\loan`i'.dta", clear

keep if t_max==6

keep if t_series==1

keep absnetloanfk

merge 1:1 absnetloanfk using ".\output_data\loan_sample_history_temp.dta"

keep if _merge==2

drop _merge

save ".\output_data\loan_sample_history_temp.dta", replace  // This only keep unmatched loans for the next round and replace the data previously saved under this filename
}

// Run the following loop on the entire sample period as indicated below

clear all

forvalues i=576(-1)576{   // change the loop to i=674(-1)576

append using ".\output_data\loan`i'.dta"
}

save ".\output_data\loansample_perf.dta", replace


// 3. Keeping One Observation for each loan 

sort absnetloanfk reportingperiodfk

by absnetloanfk reportingperiodfk: gen id=_n 

keep if id==1

drop id

gsort absnetloanfk -reportingperiodfk

by absnetloanfk: gen id=_n 

by absnetloanfk: egen max_id=max(id)

tab max_id

drop t_series t_max id max_id

// Keeping Last 6 performance observations from history end date 

gen histendyear=year(histenddate)

label variable histendyear "history end year"

gen histendmonth=month(histenddate)

label variable histendmonth "history end month"

gen histendmonthdate=mofd(histenddate)

label variable histendmonthdate "history end month date"


tostring reportingperiodfk, gen(_reportingperiodfk)

gen repyear=substr(_reportingperiodfk,1,4)

destring repyear, replace

label variable repyear "reporting month year"

gen repmonth=substr(_reportingperiodfk,5,2)

destring repmonth, replace

label variable repmonth "reporting month"

drop _reportingperiodfk

gen repdate=ym(repyear,repmonth)

label variable repdate "reporting month date"


gsort absnetloanfk -repdate

by absnetloanfk: gen id=_n 

drop if id>6

// Keep Loans whose at history end balance not missing - Round 1

gen keep=1 if id==1 & (!inlist(beginningpoolbalance,0,.) | !inlist(endingpoolbalance,0,.) | !inlist(actualendingbalance,0,.))

by absnetloanfk: egen keep_check=max(keep)

drop if keep==. & keep_check==1

// Second round

drop if id==1 & keep==.

drop keep_check

replace keep=1 if id==2 & (!inlist(beginningpoolbalance,0,.) | !inlist(endingpoolbalance,0,.) | !inlist(actualendingbalance,0,.))

by absnetloanfk: egen keep_check=max(keep)

drop if keep==. & keep_check==1

// Third round

drop if id==2 & keep==.

drop keep_check

replace keep=1 if id==3 & (!inlist(beginningpoolbalance,0,.) | !inlist(endingpoolbalance,0,.) | !inlist(actualendingbalance,0,.))

by absnetloanfk: egen keep_check=max(keep)

drop if keep==. & keep_check==1

// Fourth round

drop if id==3 & keep==.

drop keep_check

replace keep=1 if id==4 & (!inlist(beginningpoolbalance,0,.) | !inlist(endingpoolbalance,0,.) | !inlist(actualendingbalance,0,.))

by absnetloanfk: egen keep_check=max(keep)

drop if keep==. & keep_check==1

// Fifth round

drop if id==4 & keep==.

drop keep_check

replace keep=1 if id==5 & (!inlist(beginningpoolbalance,0,.) | !inlist(endingpoolbalance,0,.) | !inlist(actualendingbalance,0,.))

by absnetloanfk: egen keep_check=max(keep)

drop if keep==. & keep_check==1

// Sixth round

drop if id==5 & keep==.

drop keep_check

replace keep=1 if id==6 & (!inlist(beginningpoolbalance,0,.) | !inlist(endingpoolbalance,0,.) | !inlist(actualendingbalance,0,.))

drop if keep==.


// Checking data

sort absnetloanfk repdate

by absnetloanfk: gen idd=_n 

assert idd==1

assert !inlist( beginningpoolbalance,0,.) | !inlist(endingpoolbalance,0,.) | !inlist(actualendingbalance,0,.)

drop keep idd 

rename id repid

// Loan Balance at Termination

gen endloanbal=beginningpoolbalance if !inlist(beginningpoolbalance,0,.) & beginningpoolbalance>0

replace endloanbal=actualendingbalance if endloanbal==. & !inlist(actualendingbalance,0,.) & actualendingbalance>0

replace endloanbal=endingpoolbalance if endloanbal==. & !inlist(endingpoolbalance,0,.) & endingpoolbalance>0

drop if beginningpoolbalance<0 & actualendingbalance==0 & endingpoolbalance==0

drop if actualendingbalance<0 & beginningpoolbalance==0 & endingpoolbalance==0

drop if endingpoolbalance<0 & beginningpoolbalance==0 & actualendingbalance==0

*assert endloanbal!=. // You may want to run this test when using real data

save ".\output_data\loansample_perf.dta", replace // This replace the data previously saved under this filename


// Adding Loan Characteristics at Origination

clear all

use ".\output_data\loansample_loanhistory.dta", clear

drop year month date

merge 1:1 absnetloanfk using ".\output_data\loansample_perf.dta"

keep if _merge==3

drop _merge

merge 1:1 absnetloanfk using ".\output_data\loan_sample.dta", keepusing(absnetloanfk)

keep if _merge==3

drop _merge

sort lienposition

by lienposition: sum originalappraisedvalue

save ".\output_data\loansample_loanhistory.dta", replace // This replace the data previously saved under this filename


// IV. ADDING HPI DATA AND COMPUTING EQUITY POSITION

// 1. Adding Three-Digit Zip HPI at Loan Origination Date (from FHFA)

tostring propertyzip, gen(zipcode)

br if strlen(zipcode)<5

replace zipcode="0"+zipcode if strlen(zipcode)==4

assert strlen(zipcode)==5

gen threedigitzipcode=substr(zipcode,1,3)


order origyear, last

gen origmonth=month(loanoriginationdate)

gen origquarter=quarter(loanoriginationdate)

rename (origyear origquarter)(year quarter)

sort threedigitzipcode year quarter

merge m:1 threedigitzipcode year quarter using ".\input_data\HPI_AT_3zip.dta", keepusing(indexnsa)  // real data is publicly available (reference is in the paper)

drop if _merge==2

drop _merge

rename indexnsa hpi_orig


// 2. Using State HPI (from FHFA) at Loan Origination Date for Loans with Missing Three-digit Zip HPI 

rename propertystatefk state

merge m:1 state year quarter using ".\input_data\HPI_AT_state.dta" // real data is publicly available (reference is in the paper)

drop if _merge==2

drop _merge

gen statehpi_dum=1 if hpi_orig==.

replace statehpi_dum=0 if statehpi_dum==.

replace hpi_orig=index if hpi_orig==.

drop index

rename (year quarter)(origyear origquarter)

// 3. Adding Three-Digit Zip HPI at Loan End Date (from FHFA)

gen histendquarter=quarter(histenddate)

rename (histendyear histendquarter)(year quarter)

merge m:1 threedigitzipcode year quarter using ".\input_data\HPI_AT_3zip.dta", keepusing(indexnsa)

drop if _merge==2

drop _merge

rename indexnsa hpi_end

// 4. Using State HPI (from FHFA) at Loan End Date for Loans with Missing Three-digit Zip HPI 

merge m:1 state year quarter using ".\input_data\HPI_AT_state.dta"

drop if _merge==2

drop _merge

assert hpi_end==. if statehpi_dum==1

replace hpi_end=index if hpi_end==.

drop index

rename (year quarter)(histendyear histendquarter)

rename state propertystatefk

assert hpi_orig!=. & hpi_end!=.

// 5. Equity Position First Liens Only

gen adj_propvalue=originalappraisedvalue*hpi_end/hpi_orig

gen equity_lien1=adj_propvalue-endloanbal if lienposition==1

gen negequity_lien1=1 if equity_lien1<0

replace negequity_lien1=0 if negequity_lien1==. & lienposition==1

sort lienposition

by lienposition: tab negequity_lien1, m

sum equity_lien1 if lienposition==1, d 

save ".\output_data\loansample_loanhistory_rv.dta", replace


// V. MATCHING 2ND TO 1ST LIENS

// 1st Liens

keep if lienposition==1

save ".\output_data\loansample_loanhistory_lien1.dta", replace

keep absnetloanfk loanoriginationdate occupancystatusfk propertystatefk propertyzip propertytypefk numberofunits originalappraisedvalue propertycity lienposition originalloanbalance

gen appraisvalue=int(originalappraisedvalue/1000)

gen origdate=mofd(loanoriginationdate)

rename (absnetloanfk loanoriginationdate lienposition originalappraisedvalue propertycity originalloanbalance)(absnetloanfk1 loanoriginationdate1 lienposition1 originalappraisedvalue1 propertycity1 originalloanbalance1)

save ".\output_data\loansample_loanhistory_lien1_short.dta", replace


// 2nd Liens

clear all

use ".\output_data\loansample_loanhistory_rv.dta", clear

keep if lienposition==2

save ".\output_data\loansample_loanhistory_lien2.dta", replace

keep absnetloanfk loanoriginationdate occupancystatusfk propertystatefk propertyzip propertytypefk numberofunits originalappraisedvalue propertycity lienposition originalloanbalance

gen appraisvalue=int(originalappraisedvalue/1000)

gen origdate=mofd(loanoriginationdate)

rename (absnetloanfk loanoriginationdate lienposition originalappraisedvalue propertycity originalloanbalance)(absnetloanfk2 loanoriginationdate2 lienposition2 originalappraisedvalue2 propertycity2 originalloanbalance2)

save ".\output_data\loansample_loanhistory_lien2_short.dta", replace


// VI. IDENTIFYING LOANS WHO TERMINATION IS NOT DUE TO REFINANCING

// 1. Matching 1st and 2nd Liens

clear all 

use ".\output_data\loansample_loanhistory_lien1_short.dta", clear

joinby occupancystatusfk propertystatefk propertyzip propertytypefk numberofunits appraisvalue origdate using ".\output_data\loansample_loanhistory_lien2_short.dta"

sort absnetloanfk2

// a. Keeping 2nd Liens matched only to one 1st lien

by absnetloanfk2: gen id=_n 

by absnetloanfk2: egen idmax=max(id)

tab idmax

drop if idmax>1 & loanoriginationdate1!=loanoriginationdate2

drop id idmax

sort absnetloanfk2

by absnetloanfk2: gen id=_n 

by absnetloanfk2: egen idmax=max(id)

tab idmax

keep if idmax==1

drop id idmax

rename absnetloanfk1 absnetloanfk

// b. Keep 1st Liens matched only to one 2nd Liens

sort absnetloanfk

by absnetloanfk: gen id=_n 

by absnetloanfk: egen idmax=max(id)

tab idmax

drop if idmax>1 & loanoriginationdate1!=loanoriginationdate2

drop id idmax

sort absnetloanfk

by absnetloanfk: gen id=_n 

by absnetloanfk: egen idmax=max(id)

tab idmax

keep if idmax==1

drop id idmax

save ".\output_data\loansample_loanhistory_matched_lien1_lien2.dta", replace

// 2. Keeping Matched 2nd Liens

clear all

use ".\output_data\loansample_loanhistory_lien2.dta", clear

rename absnetloanfk absnetloanfk2 

merge 1:1 absnetloanfk2 using ".\output_data\loansample_loanhistory_matched_lien1_lien2.dta", keepusing(absnetloanfk2 absnetloanfk)

keep if _merge==3

drop _merge

order absnetloanfk, first

drop  equity_lien1 negequity_lien1

rename (loanid documenttypefk absnetloandealpk loanoriginationdate loantypefk loanpurposefk originalltv occupancystatusfk originalinterestrate originalloanbalance originalsecuritizedbalance prepaymentpenaltyflag propertystatefk propertyzip zipcode propertytypefk lienposition originalamortizationterm originalcltv debttoincomeratio pmiflag negativeamortizationflag armflag balloonflag interestonlyflag originator creditscore numberofunits originalappraisedvalue cbsa purchasepriceofproperty originalterm propertycity loanstatusfk_200712 remainingterm_200712 beginningpoolbalance_200712 endingpoolbalance_200712 actualendingbalance_200712 delinquentdaysmba_200712 currentinterestrate_200712 prepaymentsinfull_200712 liquidations_200712 repurchases_200712 daysinbankruptcy_200712 daysinforeclosure_200712 daysinreo_200712 daystoliquidation_200712 shortsaleflag_200712 cleanflagmba_200712 conformingflag_200712 beginningpoolbalance_200801 closingdate vintage assettype histstartdate histenddate bankruptcydate foreclosuredate reodate lewtanmodifieddate prepaymentdate liquidationdate repurchasedate reportingperiodfk loanstatusfk remainingterm beginningpoolbalance endingpoolbalance actualendingbalance delinquentdaysmba currentinterestrate prepaymentsinfull liquidations repurchases daysinbankruptcy daysinforeclosure daysinreo daystoliquidation shortsaleflag histendyear histendmonth histendmonthdate repyear repmonth repdate repid endloanbal threedigitzipcode origyear origmonth origquarter hpi_orig statehpi_dum histendquarter hpi_end adj_propvalue)(loanid2 documenttypefk2 absnetloandealpk2 loanoriginationdate2 loantypefk2 loanpurposefk2 originalltv2 occupancystatusfk2 originalinterestrate2 originalloanbalance2 originalsecuritizedbalance2 prepaymentpenaltyflag2 propertystatefk2 propertyzip2 zipcode2 propertytypefk2 lienposition2 originalamortizationterm2 originalcltv2 debttoincomeratio2 pmiflag2 negativeamortizationflag2 armflag2 balloonflag2 interestonlyflag2 originator2 creditscore2 numberofunits2 originalappraisedvalue2 cbsa2 purchasepriceofproperty2 originalterm2 propertycity2 loanstatusfk2_200712 remainingterm2_200712 beginningpoolbalance2_200712 endingpoolbalance2_200712 actualendingbalance2_200712 delinquentdaysmba2_200712 currentinterestrate2_200712 prepaymentsinfull2_200712 liquidations2_200712 repurchases2_200712 daysinbankruptcy2_200712 daysinforeclosure2_200712 daysinreo2_200712 daystoliquidation2_200712 shortsaleflag2_200712 cleanflagmba2_200712 conformingflag2_200712 beginningpoolbalance2_200801 closingdate2 vintage2 assettype2 histstartdate2 histenddate2 bankruptcydate2 foreclosuredate2 reodate2 lewtanmodifieddate2 prepaymentdate2 liquidationdate2 repurchasedate2 reportingperiodfk2 loanstatusfk2 remainingterm2 beginningpoolbalance2 endingpoolbalance2 actualendingbalance2 delinquentdaysmba2 currentinterestrate2 prepaymentsinfull2 liquidations2 repurchases2 daysinbankruptcy2 daysinforeclosure2 daysinreo2 daystoliquidation2 shortsaleflag2 histendyear2 histendmonth2 histendmonthdate2 repyear2 repmonth2 repdate2 repid2 endloanbal2 threedigitzipcode2 origyear2 origmonth2 origquarter2 hpi_orig2 statehpi_dum2 histendquarter2 hpi_end2 adj_propvalue2)

save ".\output_data\loansample_loanhistory_matched_matched_lien2.dta", replace // This replaces the previous data saved under the same name


// 3. Merging Matched 2nd Liens to 1st Liens

clear all 

use ".\output_data\loansample_loanhistory_lien1.dta", clear

merge 1:1 absnetloanfk using ".\output_data\loansample_loanhistory_matched_matched_lien2.dta", keepusing(absnetloanfk2 originalloanbalance2 originalsecuritizedbalance2 lienposition2 originalamortizationterm2 loanstatusfk2_200712 beginningpoolbalance2_200712 endingpoolbalance2_200712 actualendingbalance2_200712 delinquentdaysmba2_200712 beginningpoolbalance2_200801  histstartdate2 histenddate2 bankruptcydate2 foreclosuredate2 reodate2 lewtanmodifieddate2 prepaymentdate2 liquidationdate2 repurchasedate2 reportingperiodfk2 loanstatusfk2 remainingterm2 beginningpoolbalance2 endingpoolbalance2 actualendingbalance2 delinquentdaysmba2 currentinterestrate2 prepaymentsinfull2 liquidations2 repurchases2 daysinbankruptcy2 daysinforeclosure2 daysinreo2 daystoliquidation2 shortsaleflag2 histendyear2 histendmonthdate2 repyear2 repmonth2 repdate2 repid2 endloanbal2 origyear2 origquarter2 hpi_orig2 statehpi_dum2 histendquarter2 hpi_end2 adj_propvalue2)

drop _merge

sort absnetloanfk


// 4. Computing Negative Equity after Accounting for 2nd Liens up to 6 months back 

sort absnetloanfk2

gen endtimediff_lien2_lien1=histendmonthdate2-histendmonthdate

sum endtimediff_lien2_lien1, d

br absnetloanfk loanoriginationdate originalappraisedvalue histenddate endloanbal adj_propvalue equity_lien1 negequity_lien1 absnetloanfk2 histenddate2 adj_propvalue2 endtimediff_lien2_lien1 endloanbal2 originalloanbalance originalloanbalance2

gen live_lien2_dum=1 if endtimediff_lien2_lien1>=-6 & endtimediff_lien2_lien1!=.

replace live_lien2_dum=0 if live_lien2_dum==.

gen endloanbal_1st2ndlien = endloanbal

replace endloanbal_1st2ndlien = endloanbal_1st2ndlien + endloanbal2 if live_lien2_dum==1

gen equity_lien12=adj_propvalue-endloanbal_1st2ndlien

gen negequity_lien12=1 if equity_lien12<0

replace negequity_lien12=0 if negequity_lien12==.

// 5. Computing Negative Equity Using 1st Liens and CLTV

sum originalcltv, d

count if originalcltv < originalltv

sum originalltv, d 

gen origloanbal_clv = originalloanbalance/originalltv*originalcltv if originalcltv>=originalltv & originalcltv<=125

replace origloanbal_clv=originalloanbalance if originalcltv<originalltv

gen endloanbal_cltv=endloanbal/originalloanbalance*origloanbal_clv

replace endloanbal_cltv=endloanbal_1st2ndlien if live_lien2_dum==1

gen equity_cltv=adj_propvalue-endloanbal_cltv

gen negequity_cltv=1 if equity_cltv<0

replace negequity_cltv=0 if negequity_cltv==. & endloanbal_cltv!=.

// Check

count if negequity_lien1==1

count if negequity_lien12==1

count if negequity_cltv==1

// 6. Identifying Terminal Loan Status

br absnetloanfk histstartdate histenddate bankruptcydate foreclosuredate reodate lewtanmodifieddate prepaymentdate liquidationdate repurchasedate reportingperiodfk loanstatusfk beginningpoolbalance endingpoolbalance actualendingbalance remainingterm delinquentdaysmba

tab loanstatusfk, m 

gsort -histenddate

count if prepaymentdate!=.

count if prepaymentdate!=. & loanstatusfk=="P"

count if liquidationdate!=. & loanstatusfk=="L"

count if bankruptcydate!=. & loanstatusfk=="B"

count if repurchasedate!=. & loanstatusfk=="E"

count if foreclosuredate!=. & loanstatusfk=="F"

count if reodate!=. & loanstatusfk=="R"

count if bankruptcydate==. & foreclosuredate==. & reodate==. & prepaymentdate==. & liquidationdate==. & repurchasedate==. 

count if lewtanmodifieddate!=.

// Dropping Repurchases

drop if loanstatusfk=="E"

*assert repurchasedate==.

br if loanstatusfk=="E"

drop repurchasedate

br absnetloanfk histstartdate histenddate bankruptcydate foreclosuredate reodate lewtanmodifieddate prepaymentdate liquidationdate repurchasedate reportingperiodfk loanstatusfk beginningpoolbalance endingpoolbalance actualendingbalance remainingterm delinquentdaysmba

// Identifying Foreclosed Loans (REOs, liquidated, bankrupt, and foreclosed)

gen foreclosed=1 if inlist(loanstatusfk,"L","B","F","R")

replace foreclosed=0 if foreclosed==.

tab foreclosed, m 

gen paidoff=1 if loanstatusfk=="P"

replace paidoff=0 if paidoff==.

tab paidoff, m

gen active=1 if loanstatusfk=="A"

replace active=0 if active==.

* assert foreclosed + paidoff + active == 1 // You may want to run this test whe

save ".\output_data\final_sample.dta", replace


// B. ADDING REALTYTRAC AND OTHER DATA

// I. MATCHING WITH REALTYTRAC

// 1. Loans in final ABSNET Sample for matching with RealtyTrac using SAS

clear all

use ".\output_data\final_sample.dta", clear

keep absnetloanfk loantypefk loanoriginationdate loanpurposefk occupancystatusfk originalloanbalance propertystatefk propertyzip lienposition originator numberofunits originalappraisedvalue propertycity cbsa purchasepriceofproperty

saveold ".\output_data\final_sample_loans.dta", replace

clear all

use ".\output_data\final_sample.dta", clear

keep absnetloanfk repyear repmonth repdate foreclosed paidoff active

saveold ".\output_data\final_sample_loans_enddate.dta", replace


// 2. Getting Absnet-Realty Trac matched SAS data

import sas using ".\input_data\absnet_rltytr_match_twice_rr_xd.sas7bdat", clear case(lower)  // This dataset was created with the SAS dofile "realtytrac_data_dofile"

save ".\input_data\absnet_rltytr_match_twice_rr_xd.dta", replace

use ".\output_data\final_sample.dta", clear

merge 1:1 absnetloanfk using ".\input_data\absnet_rltytr_match_twice_rr_xd.dta", keepusing(absnetloanfk propertycity loanshort appraisalshort loanamount purchase sr_unique_id sr_property_id mm_state_code mm_fips_state_code mm_fips_muni_code mm_fips_county_name sr_mail_city sr_mail_state sr_mail_zip sr_buyer sr_seller sr_val_transfer sr_date_transfer sr_date_filing sr_doc_type sr_deed_type sr_tran_type sr_quitclaim sr_loan_val_1 sr_lndr_first_name_1 sr_lndr_last_name_1 sa_site_city sa_site_state sa_site_zip year month day loanoriginationdate regist_lag sr_unique_id_2 sr_buyer_2 sr_seller_2 sr_date_transfer_2 sr_tran_type_2 sr_lndr_last_name_2 zipcode_2 loanamount_2 year_2 month_2 day_2 purchase_2 date_2 enddate_diff ///
sa_property_id trans_date_asshist trans_year_asshist trans_month_asshist date_2_asshist enddate_diff_asshist origdate_asshist origdate_diff_asshist)


tab paidoff  sr_tran_type_2 if _merge==3, m 

drop _merge

replace sr_tran_type_2 = "R" if enddate_diff_asshist != . & sr_tran_type_2 == ""

save ".\output_data\final_sample_rltytrac_matched_rr_xd.dta", replace


// II. ADDING OTHER DATA SETS

clear all

use ".\output_data\final_sample_rltytrac_matched_rr_xd.dta", clear


// 1. State Foreclosure Laws

merge m:1 propertystatefk using ".\input_data\recourse_law.dta"

drop if _merge==2

drop _merge


// 2. House Price Volatiliy

drop year month day

rename (repyear repmonth)(year month)

gen quarter=1 if inlist(month,1,2,3)
replace quarter=2 if inlist(month,4,5,6)
replace quarter=3 if inlist(month,7,8,9)
replace quarter=4 if inlist(month,10,11,12)

merge m:1 threedigitzipcode year quarter using ".\input_data\HPI_AT_3zip_vol.dta", keepusing(hpi_sd)

drop if _merge==2

drop _merge

rename propertystatefk state

merge m:1 state year quarter using ".\input_data\HPI_AT_state_vol.dta", keepusing(hpi_sd_state)

drop if _merge==2

drop _merge

replace hpi_sd=hpi_sd_state if hpi_sd==.

// 3. Mortgage Rates

merge m:1 year month using ".\input_data\mortgage30us.dta", keepusing(mortgage30yr)

drop if _merge==2

drop _merge

// 4. Unemployment

merge m:1 state year using ".\input_data\emp-unemployment.dta", keepusing(unemployment)

drop if _merge==2

drop _merge

// 5. Inflation

merge m:1 year month using  ".\input_data\cpi.dta", keepusing(cpi)

drop if _merge==2

drop _merge

// 6. Median Income

// Median Income Owner 2008-2012

merge m:1 zipcode using ".\input_data\acs_medianinc_ownerrenter_200812.dta", keepusing(zipcode median_inc_owner)

drop if _merge==2

drop _merge

rename median_inc_owner medianinc_own200812

rename state statecode

merge m:1 statecode using ".\input_data\acs_medianinc_ownerrenter_200812_state.dta", keepusing(statecode median_inc_owner)

drop if _merge==2

drop _merge

replace medianinc_own200812=median_inc_owner if medianinc_own200812==.

drop median_inc_owner

// Median Income Owner 2007-11

merge m:1 zipcode using ".\input_data\acs_medianinc_ownerrenter_200711.dta", keepusing(zipcode median_inc_owner)

drop if _merge==2

drop _merge

rename median_inc_owner median_inc_owner_200716

label var median_inc_owner_200716 "Medium Household Income Owner Occupied (200716)"

replace median_inc_owner_200716=. if year>2011

count if median_inc_owner_200716==. & year<=2011

merge m:1 statecode using ".\input_data\acs_medianinc_ownerrenter_200711_state.dta", keepusing(statecode median_inc_owner)

drop if _merge==2

drop _merge

replace median_inc_owner_200716=median_inc_owner if median_inc_owner_200716==. & year<=2011

drop median_inc_owner

// Median Income Owner 2012-16

merge m:1 zipcode using ".\input_data\acs_medianinc_ownerrenter_201216.dta", keepusing(zipcode median_inc_owner)

drop if _merge==2

drop _merge

replace median_inc_owner_200716=median_inc_owner if year>2011

count if median_inc_owner_200716==.

drop median_inc_owner

merge m:1 statecode using ".\input_data\acs_medianinc_ownerrenter_201216_state.dta", keepusing(statecode median_inc_owner)

drop if _merge==2

drop _merge

replace median_inc_owner_200716=median_inc_owner if median_inc_owner_200716==.

drop median_inc_owner

save ".\output_data\study_sample_final.dta", replace


// END